﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Transactions;

namespace LGSVR.Common
{
    public abstract class DataBasePage : BasePage
    {
        /// <summary>
        /// 更新或添加影响行
        /// </summary>
        protected int Result { get; set; }

        public DataBasePage()
        {

            //if (Session["UserID"] == null)
            //{
            //    Page.ClientScript.RegisterStartupScript(this.GetType(), "out", "<script>alert('系统超时或服务器故障，请退出重新登录！');window.parent.parent.navigate('../../Logout.aspx');</script>");

            //}
        }

        #region 数据库操作
        /// <summary>
        /// 添加单表记录
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="dicParam">参数  key 为字段名   Dictionary[key] 为字段值</param>
        public virtual int Insert(string tbName, Dictionary<string, string> dicParam)
        {
            List<SqlParameter> lstParam;
            StringBuilder sbInsert;
            StringBuilder sbValue;

            sbInsert = new StringBuilder();
            sbValue = new StringBuilder();
            lstParam = new List<SqlParameter>();

            foreach (string key in dicParam.Keys)
            {
                lstParam.Add(new SqlParameter(key, dicParam[key]));

                if (sbInsert.Length > 1)
                {
                    sbInsert.Append(",");
                    sbValue.Append(",");
                }

                sbInsert.Append(key);
                sbValue.Append("@" + key);
            }

            sbInsert.Insert(0, "insert into " + tbName + " (");
            sbInsert.Append(")values(");
            sbInsert.Append(sbValue);
            sbInsert.Append(");select @@IDENTITY");


            try
            {
                
                object id = LGSVR.Common.SQLHelper.GetScale(sbInsert.ToString(), CommandType.Text, lstParam.ToArray());
                Result = Convert.ToInt32(id);
                OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "添加" + tbName + "信息", LogOpType.新增日志, DateTime.Now, "");
                return Result;
            }
            catch (Exception ex)
            {
                Result = -1;
                OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "添加" + tbName + "信息", LogOpType.程序异常日志, DateTime.Now, ex.ToString());
                return 0;
            }
        }

        /// <summary>
        /// 更新单表记录
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="dicParam">参数  key 为字段名   Dictionary[key] 为字段值</param>
        /// <param name="editWhere">更新where条件如 ID=1 </param>
        public virtual void Edit(string tbName, Dictionary<string, string> dicParam, string editWhere)
        {
            StringBuilder sbEditSQL;
            List<SqlParameter> lstParam;

            sbEditSQL = new StringBuilder();
            lstParam = new List<SqlParameter>();


            foreach (string key in dicParam.Keys)
            {
                if (sbEditSQL.Length > 1)
                {
                    sbEditSQL.Append(",");
                }

                sbEditSQL.Append(key);
                sbEditSQL.Append("=@" + key);
                lstParam.Add(new SqlParameter("@" + key, dicParam[key]));
            }


            sbEditSQL.Insert(0, "update " + tbName + " set ");
            sbEditSQL.Append(" where ");
            sbEditSQL.Append(editWhere);
            try
            {
                Result = SQLHelper.ExecuteNonQuery(sbEditSQL.ToString(), CommandType.Text, lstParam.ToArray());
                OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "修改" + tbName + "信息", LogOpType.修改日志, DateTime.Now, "");

            }
            catch (Exception ex)
            {
                Result = -100;
                OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "修改" + tbName + "信息", LogOpType.程序异常日志, DateTime.Now, ex.ToString());           
            }
        }

        /// <summary>
        /// 加载单表记录
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="sqlWhere">加载条件</param>
        /// <returns></returns>
        public virtual Dictionary<string, string> GetSigle(string tbName, string sqlWhere)
        {
            string selSQL;
            Dictionary<string, string> dicResult;
            DataTable dt;
            DataRow dr;

            dicResult = new Dictionary<string, string>();
            selSQL = "select * from " + tbName;
            if (!string.IsNullOrEmpty(sqlWhere))
            {
                selSQL += "  where " + sqlWhere;
               
            }
            dt = SQLHelper.GetDataTable(selSQL, CommandType.Text);

            if (dt != null && dt.Rows.Count > 0)
            {
                dr = dt.Rows[0];

                foreach (DataColumn item in dt.Columns)
                {
                    dicResult.Add(item.ColumnName, dr[item.ColumnName].ToString());
                }
            }
            else
            {
                dicResult = null;
            }

            return dicResult;
        }

        /// <summary>
        /// 列表分页
        /// </summary>
        /// <param name="sqlTbSrc">显示列表内容SQL</param>
        /// <param name="orderBy">排序</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="totalRS">总记录数</param>
        /// <returns></returns>
        public virtual DataTable GetList(string sqlTbSrc, string orderBy, string strWhere, int pageIndex, int pageSize, out int totalRS)
        {
            StringBuilder sbSQL;
            StringBuilder sbTotalSQL;
            DataTable dt;
            object objTotalRS;
            int tRS;
            int startIndex;
            int endIndex;

            sbSQL = new StringBuilder();
            sbTotalSQL = new StringBuilder();

            startIndex = (pageIndex - 1) * pageSize + 1;
            endIndex = pageIndex * pageSize;

            sbSQL.Append("select * from (");
            sbSQL.Append("select *,row_number() over(order by  " + orderBy + ") as ROWNUM   FROM ");
            sbTotalSQL.Append("select count(*) from ");

            if (sqlTbSrc.Contains(" "))
            {
                sbSQL.Append(" (" + sqlTbSrc + ")WW ");
                sbTotalSQL.Append(" (" + sqlTbSrc + ")WW  ");
            }
            else
            {
                sbSQL.Append(sqlTbSrc);
                sbTotalSQL.Append(sqlTbSrc);
            }
            if (strWhere.Trim() != "")
            {
                sbSQL.Append( " where 1=1"+strWhere);
                sbTotalSQL.Append(" where 1=1" + strWhere);
            }

            sbSQL.Append(")H WHERE ROWNUM BETWEEN " + startIndex.ToString() + " AND " + endIndex.ToString());

            dt = SQLHelper.GetDataTable(sbSQL.ToString(), CommandType.Text);
            objTotalRS = SQLHelper.GetScale(sbTotalSQL.ToString(), CommandType.Text);

            if (objTotalRS != null && objTotalRS.ToString() != "")
            {
                tRS = int.Parse(objTotalRS.ToString());
            }
            else
            {
                tRS = 0;
            }

            totalRS = tRS;

            return dt;
            
        }


        /// <summary>
        /// 根据条件查询某个表的所有记录
        /// </summary>
        /// <param name="tbName"></param>
        /// <param name="sqlWhere"></param>
        /// <returns></returns>
        public virtual DataTable GetListByTable(string tbName, string sqlWhere)
        {
            string selSQL;
           
            DataTable dt;

         
            selSQL = "select * from " + tbName;
            if (!string.IsNullOrEmpty(sqlWhere))
            {
                selSQL += "  where " + sqlWhere;

            }
            dt = SQLHelper.GetDataTable(selSQL, CommandType.Text); 
            return dt;
        }

        /// <summary>
        /// 删除单表记录
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="lstIds">主键</param>
        public virtual bool Delete(string tbName, string lstIds)
        {
            StringBuilder sbEditSQL;
            List<SqlParameter> lstParam ;

            sbEditSQL = new StringBuilder();

            try
            {
                if (lstIds.Trim() != "")
                {
                    using (TransactionScope tran = new TransactionScope())
                    {
                        sbEditSQL.Insert(0, "delete " + tbName + "");
                        sbEditSQL.Append(" where ID=@ID");
                        foreach (var id in lstIds.Split(','))
                        {
                            lstParam = new List<SqlParameter>();
                            lstParam.Add(new SqlParameter("@ID", id));
                            SQLHelper.ExecuteNonQuery(sbEditSQL.ToString(), CommandType.Text, lstParam.ToArray());
                        }
                        OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "删除" + tbName + "信息,IDs:" + lstIds, LogOpType.删除日志, DateTime.Now, "");
                        tran.Complete();
                        return true;
                    }                   
                }
            }
            catch (Exception ex)
            {
                OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "删除" + tbName + "信息,IDs:" + lstIds, LogOpType.删除日志, DateTime.Now, ex.ToString());
                return false;
            }
            return false;
        }

        /// <summary>
        /// 删除单表记录
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="strKeys">条件字段名</param>
        /// <param name="strValues">值</param>
        public virtual bool Delete(string tbName, string strKeys, string strValues)
        {
            StringBuilder sbEditSQL;
            List<SqlParameter> lstParam = new List<SqlParameter>();

            sbEditSQL = new StringBuilder();

            try
            {
                if (strValues.Trim() != "")
                {
                    using (TransactionScope tran = new TransactionScope())
                    {
                        sbEditSQL.Insert(0, "delete " + tbName + "");
                        sbEditSQL.Append(" where " + strKeys +"=@ID");
                        foreach (var id in strValues.Split(','))
                        {
                            lstParam.Add(new SqlParameter("@ID", id));
                            SQLHelper.ExecuteNonQuery(sbEditSQL.ToString(), CommandType.Text, lstParam.ToArray());
                        }
                        OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "删除" + tbName + "信息,IDs:" + strValues, LogOpType.删除日志, DateTime.Now, "");
                        tran.Complete();
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "删除" + tbName + "信息,IDs:" + strValues, LogOpType.删除日志, DateTime.Now, ex.ToString());
                return false;
            }
            return false;
        }

        /// <summary>
        /// 是否包含某个值
        /// </summary>
        /// <param name="tbName"></param>
        /// <param name="keyName">字段名</param>
        /// <param name="keyValue">值</param>
        /// <param name="id">当前ID</param>
        /// <returns></returns>
        public virtual bool HasKey(string tbName, string keyName,string keyValue,int id)
        {
            string selSQL;
            Dictionary<string, string> dicResult;
            List<SqlParameter> lstParam = new List<SqlParameter>();

            dicResult = new Dictionary<string, string>();
            selSQL = "select count(*) from " + tbName + " where " + keyName + "='" + keyValue + "'";
            if (id != 0)
            {
                selSQL += " and id!=@ID";
            }
            lstParam.Add(new SqlParameter("@ID", id));
            object len = SQLHelper.GetScale(selSQL, CommandType.Text, lstParam.ToArray());
            return Convert.ToInt32(len) > 0 ? true : false;

        }

        /// <summary>
        /// 修改数据状态
        /// </summary>
        /// <param name="tbName"></param>
        /// <param name="state"></param>
        /// <param name="lstId"></param>
        public virtual bool UpdateState(string tbName, object state, string lstId)
        {
            StringBuilder sbEditSQL;
          
            sbEditSQL = new StringBuilder();

            sbEditSQL.Insert(0, "update " + tbName + " set state=@state");
            sbEditSQL.Append(" where id=@ID");
            try
            {
                using (TransactionScope tran = new TransactionScope())
                {
                    foreach (var id in lstId.Split(','))
                    {
                        List<SqlParameter> lstParam = new List<SqlParameter>();
                        lstParam.Add(new SqlParameter("@state", state));
                        lstParam.Add(new SqlParameter("@ID", id));
                        Result = SQLHelper.ExecuteNonQuery(sbEditSQL.ToString(), CommandType.Text, lstParam.ToArray());
                    }
                    OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "停用" + tbName + "信息,IDs:" + lstId, LogOpType.停用日志, DateTime.Now, "");
                    tran.Complete();
                    return true;
                }
            }
            catch (Exception ex)
            {
                OpLogBLL.SaveLog(Session["UserID"].ToString(), LogLoginType.W, "停用" + tbName + "信息,IDs:" + lstId, LogOpType.停用日志, DateTime.Now, ex.ToString());
                return false;
            }
          
        }
        #endregion 

      
    }
}
